SELECT within SELECT

how can I use select without inner joins?

[madivad]

I know this is strictly not PHP, but I'm using PHP to access a MySQL database, and I'm sure other PHP programmers will know how to fix my query here, and there didn't seem to be a MySql section.

Anyway, it's a simple enough join question, but I've never understood them. MANY years ago, someone showed me how to do this without joins, and I would be much appreciative if someone could show me again ;^)

Ok, this is what I have (2 tables):

"tbl_links"
with fields: link_id, link_url, link_descr, link_cat_fk

(the last one is a link to the cat_id of the cat' table)

"tbl_category"
with fields: cat_id, cat_title, cat_desc

I want to display just the list of links, sorted by categories, but I want the categories to be in alphabetical order.

Seperately the SELECTS are:

"SELECT cat_id FROM tbl_category ORDER BY cat_title"

and

"SELECT * FROM tbl_links ORDER_BY link_cat_fk" <- not in alphabetical order, but sorted by groups

Now there was a way of micing these two quesries together to get the result. Can anyone help?

Thanks

[coopster]

Welcome to WebmasterWorld, madivad!

>>MANY years ago, someone showed me how to do this without joins

I'm not sure if you are referring to a Cartesian product, which actually is a JOIN, or if you are referring to subquerys/subselects...?

[coopster]

With a Cartesian product, although the actual keyword, JOIN, may not be present, there is still a JOIN occurring. I seen this explained once using numbers so I'll try to do the same. Take, for example, two simple tables:
(OFF TOPIC LINK: Carrying on a bit of a French lesson we've been getting lately ;) [webmasterworld.com] ):

english 
----------- 
¦nbr¦value¦ 
----------- 
¦ 1 ¦  one¦ 
¦ 2 ¦  two¦ 
¦ 3 ¦three¦ 
----------- 
  
french 
------------ 
¦nbr¦ value¦ 
------------ 
¦ 2 ¦  deux¦ 
¦ 3 ¦ trois¦ 
¦ 4 ¦quatre¦ 
----------- 

If you were to select from both tables without joining them in the WHERE clause, you would end up with a Cartesian product, which is every possible combination of both sets of rows in each table.

SELECT * FROM english, french; 
  
---------------------- 
¦nbr¦ value¦nbr¦value¦ 
---------------------- 
¦ 2 ¦  deux¦ 1 ¦  one¦ 
¦ 3 ¦ trois¦ 1 ¦  one¦ 
¦ 4 ¦quatre¦ 1 ¦  one¦ 
¦ 2 ¦  deux¦ 2 ¦  two¦ 
¦ 3 ¦ trois¦ 2 ¦  two¦ 
¦ 4 ¦quatre¦ 2 ¦  two¦ 
¦ 2 ¦  deux¦ 3 ¦three¦ 
¦ 3 ¦ trois¦ 3 ¦three¦ 
¦ 4 ¦quatre¦ 3 ¦three¦ 
---------------------- 

Now, if we were to specify a WHERE clause, then our JOIN would be selecting records which would not include any rows from either table that do not have a matching row in the other table according to the condition specified in the WHERE clause.

SELECT * FROM english, french WHERE english.nbr = french.nbr; 
  
---------------------- 
¦nbr¦ value¦nbr¦value¦ 
---------------------- 
¦ 2 ¦  deux¦ 2 ¦  two¦ 
¦ 3 ¦ trois¦ 3 ¦three¦ 
---------------------- 

At this point, without even using the INNER JOIN keyword, we have actually created an INNER JOIN.

Is this what you are referring to? If not, we'll take a look at subqueries, which are only available in MySQL version 4.1 and later.

[madivad]

Yeah, I kinda think that's what I was after. I struggled at it for many hours, and the penny finally dropped, and I couldn't understand why I couldn't see it right at the start. What I had in my mind wasn't what I was after, and I solved it with a WHERE clause. This was what I used:

SELECT *
FROM tbl_links, tbl_category
WHERE tbl_category.cat_id = tbl_links.link_cat_fk
ORDER BY cat_title, link_desc1"

This does exactly what I was after. I can't think of the eact query I needed to perform once before, but it involved several SELECTS nested within one another, the result of one SELECT would feed the parent one. Sounds a bit like what you wee going to offer with the sub-queries.

Can you enlighten me, and give some example?

Regards Dave

[coopster]

Nice work, you nailed the query spot-on. Good for you!

As I stated earlier, starting with version 4.1, MySQL supports all subquery [mysql.com] forms and operations which the SQL standard requires, as well as a few features which are MySQL-specific. If you were going to rewrite your query with a subquery statement in MySQL, first you are going to have to check your version:

SELECT VERSION(); 
+------------------------+ 
¦ version()              ¦ 
+------------------------+ 
¦ 4.1.1a-alpha-max-debug ¦ 
+------------------------+ 
1 row in set (0.09 sec) 

If you aren't running version 4.1 or higher, you aren't going to be able to use subqueries and you'll have to stick with what you have or read the manual pages regarding Rewriting Subqueries for Earlier MySQL Versions [mysql.com].

There are quite a few examples in the links provided. Personally, I'd stick with the JOIN you have. It's clean and easy to understand and efficient.