lard Posted September 14, 2014 Share Posted September 14, 2014 Is there a way to add two tables together but effectively underneath each other? So user 1 has a catalogue, as does user 2. The table structure is identical. There is a join from the user table to a details table. I effectively want to stack user 1 table on top of user 2 table and then join the whole lot with the details table. Each of the users will have some duplicate values - so user1 table and user2 table could both need to join together with the same entry of the details table. Does that make sense.....? CURRENTLY:SELECT *FROM user1catalogue as t1JOIN details as t2ON t1.id=t2.idetcAIMING AT SOMETHING LIKE THIS:SELECT *FROM user1catalogue AND user2catalogue as t1JOIN details as t2ON t1.id=t2.idetc Link to comment Share on other sites More sharing options...
niche Posted September 14, 2014 Share Posted September 14, 2014 You'll want to use this: http://www.w3schools.com/sql/sql_insert_into_select.asp with an ORDER BY that places the results of one table above another. You might need to add a column to do that sort. Link to comment Share on other sites More sharing options...
lard Posted September 14, 2014 Author Share Posted September 14, 2014 (edited) Thanks but I don't think you've understood what I'm after. I don't want to add one table to another permanently. They need to remain separate entities. What I'm aiming at is having check boxes to select which catalogues to include in the results, so which tables to include in the query. Edited September 14, 2014 by lard Link to comment Share on other sites More sharing options...
Ingolme Posted September 15, 2014 Share Posted September 15, 2014 I think the UNION ALL operator is what you're after: http://www.w3schools.com/sql/sql_union.asp Link to comment Share on other sites More sharing options...
lard Posted September 15, 2014 Author Share Posted September 15, 2014 Thanks Foxy, that does appear to be what I need. I'm now struggling with the syntax to treat it as a single table for the purposes of a join. This is what I've got: SELECT * FROM _10003_catalogue as t1 JOIN details as t2 ON t1.id=t2.id This is what I'm aiming at: SELECT * FROM (_10003_catalogue UNION ALL _10004_catalogue) as t1 JOIN details as t2 ON t1.id=t2.id I've tried putting parentheses in various places, and leaving them out, and just can't get it to work! Link to comment Share on other sites More sharing options...
Ingolme Posted September 15, 2014 Share Posted September 15, 2014 The page I linked you to has the proper syntax. You use the union between two different select queries. Copied from the page I just linked you to: SELECT City, Country FROM CustomersWHERE Country='Germany'UNION ALLSELECT City, Country FROM SuppliersWHERE Country='Germany'ORDER BY City; Link to comment Share on other sites More sharing options...
lard Posted September 15, 2014 Author Share Posted September 15, 2014 It's then adding a join where my syntax is falling down though, as soon as I add the join in I get nothing... Link to comment Share on other sites More sharing options...
lard Posted September 15, 2014 Author Share Posted September 15, 2014 Thanks for you help, but finally got it thanks to NogDog on webdeveloper.com, anyone interested can read both responses...http://www.webdeveloper.com/forum/showthread.php?300723 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now