SQL Question

I have two tables,

  1. “People” - which contains two columns “first_name” and “last_name”- and
  2. “Info” - which contains two columns “tagtype” and “myinfo”.
    *Both tables have a third column, “myid” with key ID that IS unique on the People table but is not necessarily unique for the Info table.

The “People” table is pretty obviously names of people. The Info table contains different information items for the people - e.g. tagtype=“Phone”, myinfo=“867.5309”

Some people have multiple phone numbers listed. I want to list ONE phone number per person and only ONE row per person. I thought this would do the trick:

SELECT DISTINCT People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People JOIN Info On People.myid=Info.myid WHERE tagtype="Phone"

but this does not work. The command I used lists each person multiple times if they have multiple phone numbers.

Any ideas of how to get the query I want? Obviously I can accomplish this in RB but there has to be a way to get this query done in SQL directly.

In MySQL I would do this (you didn’t specify what flavour of SQL you’re using). Not tested btw - this is off the top of my head.

SELECT DISTINCT People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People JOIN Info On People.myid=Info.myid WHERE tagtype="Phone" GROUP BY People.myid

From top of my head too…

SELECT DISTINCT People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People, Info where People.myid = Info.myid AND Info.tagtype="Phone"

[quote]@Adelar Duarte From top of my head too…

SELECT DISTINCT People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People, Info where People.myid = Info.myid AND Info.tagtype=“Phone”

I’m pretty sure that would still return multiple rows which is what the OP is trying to avoid in this case.

@Judy Esau Some people have multiple phone numbers listed. I want to list ONE phone number per person and only ONE row per person…

[/quote]

SELECT  People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People, Info where People.myid = Info.myid AND Info.tagtype="Phone" group by info.tagtype

[quote=172675:@Richard Brown]In MySQL I would do this (you didn’t specify what flavour of SQL you’re using). Not tested btw - this is off the top of my head.

SELECT DISTINCT People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People JOIN Info On People.myid=Info.myid WHERE tagtype="Phone" GROUP BY People.myid [/quote]

I just tested and this works. just swell.

Wow thanks! So

SELECT DISTINCT People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo FROM People JOIN Info On People.myid=Info.myid WHERE tagtype="Mobile" OR tagtype like "%hone%" GROUP BY People.myid ORDER BY last_name

This got the job done in terms of providing NO MORE THAN one entry per person but it did not provide an entry for those people that had NO phone number. I tried using FULL OUTER JOIN and LEFT JOIN in addition to simply join. Only Join seems to generate a non-null result.

Note that I am using the SQLite that comes in Xojo 2014 R2.1. I coded this all in RB.

I’m getting closer. The ultimate goal is to have one row per person and for that row to contain one phone IF we have a phone number in the DB. If no phone number is on file, then just the name appears. If we multiple phone numbers on file, then just one number.

Out of interest is there any good reason why you couldn’t just have one table that had a column for every possible piece of information that you might want to store about about somebody?

Try this:

SELECT
  People.myid, People.first_name, People.last_name, Info.tagtype, Info.myinfo
FROM
  people LEFT JOIN info ON info.myid = people.myid
WHERE
  info.tagtype = 'phone' OR info.tagtype IS NULL
GROUP BY 
  people.myid
ORDER BY
  last_name

Left join should work. Move the test for ‘phone’ into the join.

select people.myid, people.first_name, people.last_name, info.tagtyp, info.myinfo from people
left join info on info.myid = people.myid and info.tagtype = 'phone'
group by people.myid
order by people.last_name

You dont know how many email or phone number columns you need
I personally have something like 15 email addresses

[quote=172698:@Norman Palardy]You dont know how many email or phone number columns you need
I personally have something like 15 email addresses[/quote]

I understand that possibility. I was purely positing that the OP could perhaps make his programming life easier by limiting the possibilities if it fell within the bounds of his requirements. Often, just the suggestion of an alternative approach can form the basis of a better solution as I’m sure a guy of your experience will know all too well.

In our client DB for example I decided it was much easier to have phone1, phone2, phone3, phone4 columns than to have a linked table with poentially infinite phone numbers. If I can’t get hold of them on one of 4 phone numbers then they’re probably too dodgy for me to want to business with anyway! :wink:

Hello.

That SGDB have.

With Sql Server can do so

;
With t
As
(
Select myid, tagtyp, myinfo, ROW_NUMBER() Over (Partition By myid Order by myid) as num
From info
Where tagtype = ‘phone’
)
Select p.myid, p.first_name, p.last_name, t.tagtyp, t.myinfo
From people p Left Join
t On p.myid = t.myid and t.num = 1

Mauricio

[quote=172700:@Richard Brown]I understand that possibility. I was purely positing that the OP could perhaps make his programming life easier by limiting the possibilities if it fell within the bounds of his requirements. Often, just the suggestion of an alternative approach can form the basis of a better solution as I’m sure a guy of your experience will know all too well.

In our client DB for example I decided it was much easier to have phone1, phone2, phone3, phone4 columns than to have a linked table with poentially infinite phone numbers. If I can’t get hold of them on one of 4 phone numbers then they’re probably too dodgy for me to want to business with anyway! ;-)[/quote]

I’d have a related table that is “contact info” with a few columns (type, value and who its related to)
That way I can shove in an email, a phone, messages handle, skype handle, a twitter handle or whatever else might come along without having to alter my database schema to accommodate it