Hi,
I am switching to Linux and I am new to MySQL.
I can do this in MS SQL:
Given a persons DB and a weddings DB:
persons : ID, NAME
weddings: ID, MANID, WOMANID, DATE
SELECT DATE,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedding.MANID) AS MANNAME,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedding.WOMANID) AS WOMANNAME
FROM WEDDINGS
Result:
DATE MANNAME WOMANNAME
2000-01-01 John Mary
2000-01-05 Johnny Betty
How can I do that in MySQL 4.1 ?
How can I do that in previous MySQL versions ?
Mickael.
8  22934 
On 30 Nov 2003 09:54:50 -0800, fa*****@hotmail.com (Mickael
Faivre-Macon) wrote: How can I do that in MySQL 4.1 ?
How can I do that in previous MySQL versions ?
Sub-selects are a new feature in 4.1.
They are not supported in any previous version.
Chuck Gadd http://www.csd.net/~cgadd/aqua
On 30 Nov 2003 09:54:50 -0800, fa*****@hotmail.com (Mickael
Faivre-Macon) wrote: How can I do that in MySQL 4.1 ?
How can I do that in previous MySQL versions ?
Sub-selects are a new feature in 4.1.
They are not supported in any previous version.
Chuck Gadd http://www.csd.net/~cgadd/aqua
Hi Chuck,
Thank you for your reply. Sub-selects are a new feature in 4.1.
They are not supported in any previous version.
Yes, but does that means there is no alternative ? Sometimes we can
use JOINs as alternative in lieu of subqueries. What about this
particular exemple ? Is it possible ?
And I didn't see any example of subquery in a select statement for
4.1.
Mickael.
Hi Chuck,
Thank you for your reply. Sub-selects are a new feature in 4.1.
They are not supported in any previous version.
Yes, but does that means there is no alternative ? Sometimes we can
use JOINs as alternative in lieu of subqueries. What about this
particular exemple ? Is it possible ?
And I didn't see any example of subquery in a select statement for
4.1.
Mickael.
Mickael Faivre-Macon wrote: Hi,
I am switching to Linux and I am new to MySQL.
I can do this in MS SQL:
Given a persons DB and a weddings DB:
persons : ID, NAME
weddings: ID, MANID, WOMANID, DATE
SELECT DATE,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedding.MANID) AS MANNAME,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedding.WOMANID) AS WOMANNAME
FROM WEDDINGS
Result:
DATE MANNAME WOMANNAME
2000-01-01 John Mary
2000-01-05 Johnny Betty
How can I do that in MySQL 4.1 ?
How can I do that in previous MySQL versions ?
Mickael.
Try something like
SELECT w.DATE, p1.Name AS MANNAME, p2.Name AS WOMANNAME FROM WEDDINGS w
LEFT JOIN persons p1 ON w.MANID=p1.ID LEFT JOIN persons p2 on
w.WomanID=p2.ID
Bruce
Mickael Faivre-Macon wrote: Hi,
I am switching to Linux and I am new to MySQL.
I can do this in MS SQL:
Given a persons DB and a weddings DB:
persons : ID, NAME
weddings: ID, MANID, WOMANID, DATE
SELECT DATE,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedding.MANID) AS MANNAME,
(SELECT NAME FROM PERSONS WHERE persons.ID=wedding.WOMANID) AS WOMANNAME
FROM WEDDINGS
Result:
DATE MANNAME WOMANNAME
2000-01-01 John Mary
2000-01-05 Johnny Betty
How can I do that in MySQL 4.1 ?
How can I do that in previous MySQL versions ?
Mickael.
Try something like
SELECT w.DATE, p1.Name AS MANNAME, p2.Name AS WOMANNAME FROM WEDDINGS w
LEFT JOIN persons p1 ON w.MANID=p1.ID LEFT JOIN persons p2 on
w.WomanID=p2.ID
Bruce
I found out how to use JOINS for my problem.
SELECT unions.*, E1.FIRSTNAME AS FIRSTFIRSTNAME, E1.LASTNAME AS
FIRSTLASTNAME, E2.FIRSTNAME AS SECONDFIRSTNAME, E2.LASTNAME AS
SECONDLASTNAME
FROM unions
LEFT OUTER JOIN entries E1 on E1.ID=unions.FIRSTID
LEFT OUTER JOIN entries E2 on E2.ID=unions.SECONDID
ORDER BY DATE DESC
Mickael.
I found out how to use JOINS for my problem.
SELECT unions.*, E1.FIRSTNAME AS FIRSTFIRSTNAME, E1.LASTNAME AS
FIRSTLASTNAME, E2.FIRSTNAME AS SECONDFIRSTNAME, E2.LASTNAME AS
SECONDLASTNAME
FROM unions
LEFT OUTER JOIN entries E1 on E1.ID=unions.FIRSTID
LEFT OUTER JOIN entries E2 on E2.ID=unions.SECONDID
ORDER BY DATE DESC
Mickael.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Benjamin Dickgießer |
last post by:
Hi, I want to create a sql query but don't know if this is possible with
mysql.
The Query should do the following:
Select all db entries from table in which entry a is smaller than the number...
|
by: Bob Bedford |
last post by:
I've a table on mysql:
code1 tinyint, code2 tinyint, notcode1 varchar(20),notcode2
varchar(20),label
I do have some records:
values(1,null,null,null,'text1');...
|
by: red85 |
last post by:
hello i have mysql 4.1 with win2000 SP3, i know that it is only an
alpha and i don't know if someone else has already posted this
problem:
when i execute this sql
UPDATE tableX SET...
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest...
|
by: Bing Wu |
last post by:
Hi all,
I am running a database containing large datasets:
frames: 20 thousand rows,
coordinates: 170 million row.
The database has been implemented with:
IBM DB2 v8.1
|
by: Sinan Osan |
last post by:
Hello,
I want to select the users from 'userTBL' which does not have any
property in 'live_properties' table, Both table has a same column
called 'username'
I have 2 tables called...
|
by: Diogenes |
last post by:
Kind readers,
I am currently developing an app with PHP 5 & MySql 5.
The following select statement works as expected from
the MySql command line but produces an error when run from PHP.
...
|
by: timn |
last post by:
I have a query in Access that uses a subquery, I would like some help on translating it into SQL as a subquery so I can use it in an application.
the queries in Access are
the subquery......
|
by: yuan22m |
last post by:
Hi
I have encounter problem in mysql with my query regarding subquery support. The IN and NOT IN are not supported.
my client's server is running the old version of mysql and he cant upgrade...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
