SQL search from 2 or more tables

[cheryl]

Hi! i have member, nonmember & non_ind_member table plus a client table.

how can i do a select statement to join member & client, nonmember & client, and non_ind_member & client and retrieve the members' code, members' name and clients' name?

i want to get 4 similar fields from the 3 tables each, where the clientCode on the 3 members tables is a foreign key to clientCode on client table.

now i can only get from 1 table. how to get from 3 more tables?

anybody here can help?

[Kid Charming]

Are you looking for people who are in all three tables, or are just trying to get a list of everyone from each table?

[cheryl]

Are you looking for people who are in all three tables, or are just trying to get a list of everyone from each table?

more like looking for people in all 3 tables... i've tried using union, but it seems that it lists every data from the tables. not specific if i enter something.

[Kid Charming]

SELECT
m.column
,n.column
,ni.column
FROM
client
INNER JOIN
member m
ON
m.clientCode = c.clientCode
INNER JOIN
nonmember n
ON
n.clientCode = c.clientCode
INNER JOIN
non_ind_member ni
ON
ni.clientCode = c.clientCode


The above should give you all entries that exist in all four tables only. If that's not exactly what you want, post again and we can refine it.

[cheryl]

SELECT
m.column
,n.column
,ni.column
FROM
client
INNER JOIN
member m
ON
m.clientCode = c.clientCode
INNER JOIN
nonmember n
ON
n.clientCode = c.clientCode
INNER JOIN
non_ind_member ni
ON
ni.clientCode = c.clientCode


The above should give you all entries that exist in all four tables only. If that's not exactly what you want, post again and we can refine it.

i tried this block of code:
$search_query = "select m.memCode,m.memName,n.nmCode,n.nmName,ni.nimCode,ni.nimName,c.clientName
From member as m, nonmember as n, nimember as ni, client as c
Inner Join member m On m.clientCode = c.clientCode
Inner Join nonmember n On n.clientCode = c.clientCode
Inner Join nimember ni On ni.clientCode = c.clientCode";

but when i search it gives me this error: Not unique table/alias: 'm'

why is this so?? cuz i have to select from member, nonmember & nimember for members' code and name + the name of the client from a clients' table.

[Kid Charming]

Your JOIN clauses are considered part of your FROM declaration; you don't want to list your tables before you join them. Technically, you're joining everything twice -- your error's coming from trying to use the same alias for two different tables. You should only have client between FROM and the first INNER JOIN.

[cheryl]

it still doesn't output the specific data i entered to search.

example, i search for danny, all dannys from member,nonmember & nimember will be shown.

and now it outputs the organisation code instead of organisation name. how come??

can this be solved in a couple of hours? i need to show it to my project sup.

[Kid Charming]

Okay, let's back up a bit and make sure we're on the same page. You want to enter a search term ('danny') and then get all the members named danny, all the nonmembers named danny, and all the non_ind members named danny, and keep them separated? And all the dannys, no matter which of these three tables they come from, will have clientCodes in client?

[cheryl]

Okay, let's back up a bit and make sure we're on the same page. You want to enter a search term ('danny') and then get all the members named danny, all the nonmembers named danny, and all the non_ind members named danny, and keep them separated? And all the dannys, no matter which of these three tables they come from, will have clientCodes in client?

yes. would want to display all dannys from all tables as well as the clientName. clientCode is a Foreign Key in 3 members table. so a client has to exist before any member exist.

would u like me to email you the entire search form to you??

[Kid Charming]

Ok, try this:


SELECT
'member' AS whattable
,m.memCode AS code
,m.memName AS name
,c.clientName AS cname
FROM
client c
INNER JOIN
member m
ON
m.clientCode = c.clientCode
WHERE
m.memName = 'danny'
UNION ALL
SELECT
'nonmember' AS whattable
,n.nmCode AS code
,n.nmName AS name
,c.clientName AS cname
FROM
client c
INNER JOIN
nonmember n
ON
n.clientCode = c.clientCode
WHERE
n.nmName = 'danny'
UNION ALL
SELECT
'nonindmember' AS whattable
,ni.nimCode AS code
,ni.nimName AS name
,c.clientName AS cname
FROM
client c
INNER JOIN
non_ind_member ni
ON
ni.clientCode = c.clientCode
WHERE
ni.nimName = 'danny'


Since you want to keep the tables separate, a UNION'll be your best bet (you may want to also consider just running each query separately for simplicity/clarity's sake). I've added the 'x' as whattable to your select to give you a field your display code can use to differentiate between your three tables. Also note the repeated aliases for each table -- this will allow you to use the same block of code to display all three table results by allowing it to use a common name to refer to each table's fields, even though they are named differently in the db.

[cheryl]

thanks. i will just give it a try.