MYSQL :: Select Data From 2 Tables (join) REGARDLESS Of One Table Not Containing A Row

SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity.

Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ?

How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?

View Replies !

Select And Join For Multiple Tables
I have five tables in my database, there are actually NO common fields
between them, not even a KEY or ID or anything like that, except for
the "body" of a blob field. and that text is not identical, just a
portion of that text is identical.

each table has 5 fields, all different except the blob, which is
called "message", so normally I use something like:

select * from table1 where message like '%apple%';

to query this table, and the same goes for table 2, except the blob is
different, table 2 normally is like this:

select * from table2 where message like '%customerid=453%';

It's impossible to change the data in these fields (which would be the
best option), but there is one common element between them in the
message blob.
What I want to do is something like this:

select * from table1, table2 where message like '%order=100%';

however only one table will have that order, either table1 or table2,
but never both, and theres no way to tell which of the tables will
actually have the text.

In other words, I want to search a bunch of tables for common text
without having to actually submit the query five times, because the
list of elements to search is about 25,000 items... I'd rather submit
25,000 queries than 125,000 queries.

View Replies !

SELECT From Two Tables + LEFT JOIN
Since MySQL v5 was released our script stoped working due to mysql query errors.
i.e:
SELECT a.id
FROM table1 a, table2 b
LEFT JOIN table3 c ON (c.id = a.id)

This will return error:
Unknown column 'a.id' in 'on clause'

To solve this we can do in two ways:

1) change tables order in FROM statement:
SELECT a.id, b.id, c.id
FROM table1 b, table2 a
LEFT JOIN table3 c ON (c.id = a.id)

Or
2) use brackets in FROM statement:
SELECT a.id, b.id, c.id
FROM (table1 a, table2 b)
LEFT JOIN table3 c ON (c.id = a.id)

Both ways works, but I have question to the experts: which way is the most optimized/better?

View Replies !

Conditional Select/Join Across Three Tables
transactions
id, customer_id, customer_valid, amount, processed_date

valid_customers
id, first_name, last_name

invalid_customers
id, first_name, last_name

in the transactions table, the customer_valid is a boolean value which indicates whether the customer record resides in valid_customers or invalid_customers and i have achieved the information i want via this method ...

View Replies !

Getting Data Out Of Multiple Tables And Join
I have two separate querys, but what I want to do is make one query from it. To get data from different tables. Code:

View Replies !

Join 2 Tables And Select Only Most Recent Records
I have been wrestling with a way to select only the most recent records for a group of clients without using a subselect. I have two tables: cdp_clients and cdp_level_records related by 'admitid'. For each cdp_client there are multiple cdp_level_records, each with a different 'level_date'. I want to select only the most recent cdp_level_record for each client in cdp_clients.

View Replies !

Selecting Data From More Than One Table (not Join)
I have a database with a bunch of tables, including two tables that store messages. These two tables have different names, but are identical in structure.

In my app, a user can run a report that selects messages from both of these tables. This is currently achieved by running two selects and printing out the details.

However I would now like to sort and limit (i.e. show messages 1-25 and have a link to show the next 25 etc).
I can do this in the code by dumping the combined results in an array and then sorting/limiting the output, but this will cause problems if the number of messages gets too big and the array size becomes too large for the memory in the server.
Also it doesn't help any with optimization and won't speed up the query time as I won't be able to use the LIMIT clause in the SQL!

So what I want to know is whether it is possible in SQL to select data from more than one table, given that the two (or more) tables to be joined will be identical in structure? If so, can I perform the normal ORDER BY and LIMIT functions?

View Replies !

Join Table With Empty Data
I'm a novice, and am having issues get a JOIN to work properly. The following code is correct for pulling field values that HAVE a value, but when a field i am requesting has a value like Null, Space, or just empty, the whole query comes back empty.

It was suggested i use TRIM in my call, but i'm not exactly sure how to use properly.

As far as each table, the fields that are empty are set to ALLOW NULL.
PHP

Select
company.company_name,
company.company_username,
company.company_clients_served,
company.company_status,
note.note_text,
address.address_street1,
address.address_street2,
address.address_zip,
address.address_zip_ext,
county.county_id,
city.city_name,
taxes_states.state_id,
email.email_address,
url.url_path,
url.url_name,
company_type.company_type_id
From
company
Inner Join note ON company.note_id = note.note_id
,
address
Inner Join connector_address ON company.company_id = connector_address.ref_id AND connector_address.address_id = address.address_id
Inner Join county ON address.county_id = county.county_id
Inner Join city ON address.city_id = city.city_id
Inner Join taxes_states ON address.state_id = taxes_states.state_id
Inner Join connector_email ON company.company_id = connector_email.ref_id
Inner Join email ON connector_email.email_id = email.email_id
Inner Join connector_url ON company.company_id = connector_url.ref_id
Inner Join url ON connector_url.url_id = url.url_id
,
company_type
Inner Join connector_company ON company_type.company_type_id = connector_company.company_type AND company.company_id = connector_company.company_id
Where company.company_id = ཋ'

View Replies !

Join 3 Tables, Limit Results From 3rd Table
Yet again I have a query about joins and even though I keep reading I cannot find a solution for my problem that is relevant or that I understand.

I have 3 tables (I have shortened the number of fields to those that are relevant):

category:

Quote:

catid

product:

Quote:

prodid
catid
prodname

prodimgs:

Quote:

imgid
prodid
imgname

I can select all products per category and I can select all images per product, but where I am having the problem is selecting all products for one category and at the same time selecting all the images per product and limiting the number of images displayed to one without limiting the products to one.

I have tried various joins and tested a lot, getting different results but none of them are returning what I need.

Select all products for a category:

PHP

SELECT prodid, prodname FROM product WHERE catid='$this->catid'

Works fine.

Select all images per product per category and limit results of image to 1:

PHP

SELECT product.prodid, product.catid, product.prodname, prodimgs.imgname FROM product, prodimgs WHERE product.catid ='$this->catid' AND prodimgs.prodid = product.prodid LIMIT 1

This returns only one product and one image per category despite there being more.

Using the same code as above but removing the limit I get a display of all images per product with the product name repeated over and over.

I know I need to use joins somehow but I have tried various ways and tested the sql but am always getting an error on the joins and I don't know how or where to put the limit so that it is only applied to the prodimgs table.

So what I want is:

1. User click on a category

2. On the next page all product names linked to the category are displayed, but once only.

3. Next to each product name, one image related to that product is to be displayed if an image is stored in the prodimgs table

View Replies !

SELECT With Three Joined Table Including A SELF JOIN
Here are my Tables

Tickets
-------
id

Notes
-----
NoTicket
epoch
note

What I want i select all the tickets, but showing only the most recent
note to that ticket.

I have tried:

SELECT t1.id,t2.epoch,t2.note FROM Tickets t1 LEFT JOIN Notes t2 ON
t1.id = t2.NoTicket INNER JOIN Notes t3 ON t2.NoTicket = t3.NoTicket
HAVING t2.epoch = MAX(t3.epoch);

But it always give me an empty set.

I'm using mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686)

I'm sure i'm close to it, but can't find out.

View Replies !

Select Data From 3 Tables
How to select data from three tables with one query?

table1 {id,field1}
table2 {id2,field2}
table3 {id,id2}

I know the 'field1'. In the 'table3' there are connections with the 'table1' and 'table2' id's defined. So i have to find out the 'id' for the 'field1' in the 'table1', then to look in 'table3' for the 'id2' that is connected with the 'id' and in the end return 'field2' with the found 'id2'.

View Replies !

Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores:

TABLE: feedback

identities_id int(10) <- contains the user id which is stored in another table
feedback int(10) <- contains the feedback score for a specific date and time
date_recorded datetime <- date the feedback was captured
I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results):

mysql
SELECT user1.feedback AS user1feedback
, user2.feedback AS user2feedback
, UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded
FROM feedback as user1
LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded
WHERE user1.identities_id = 1
OR user2.identities_id = 2
AND user1.date_recorded > NOW() - INTERVAL 30 DAY
ORDER BY date_recorded ASC

This returns virtually the same data for both users which means I am not distinguishing the two properly.

View Replies !

How To Create A Join/relationship Table That Maps To Many Tables
Let's say I had the following table structure:

CREATE TABLE customer
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service1
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service3
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service
(
subscription_id INT UNSIGNED NOT NULL,
service_type TINYINT UNSIGNED NOT NULL,
service_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service_type, service_id)
) ENGINE = INNODB;

CREATE TABLE service_type
(
id TINYINT UNSIGNED NOT NULL,
service_table_name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
INSERT INTO service_type (id, service_table_name) VALUES
(1, 'service1'),
(2, 'service2'),
(3, 'service3');
I need a way to map Customers to Subscriptions to Services.

Please keep in mind that one subscription record can be related to many services: even services of the same type.

The above structure would work, but I wouldn't be able to make it transactional. The problem lies with the subscription_2_service table's service_id column. That column doesn't actually reference any other table. It's conditional, meaning its value changes depending on what the service type column value is.

This makes it impossible to implement transactions because the service_id column references nothing concrete.

I considered another way of doing the above by sticking a subscription_id column in all three service tables. I don't like this approach because not all services will require a subscription. This would result in many NULL values and tight coupling between the subscription and service tables. It also seems sloppy/scattered.

I also considered a third way of creating subscription_2_service tables, such as:

CREATE TABLE subscription_2_service1
(
subscription_id INT UNSIGNED NOT NULL,
service1_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service1_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service2
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service3
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;
This third method above seems ridiculous.

The third approach does decouple the subscriptions and services from one another. Services aren't aware that they're part of a subscription and subscriptions aren't aware of what services have subscribed to them. This method still seems bloated and wrong though.

View Replies !

Select * From One Table And Select Some Columns From Other Tables
I am trying to figure out how to select * from one table and select some columns from a couple other tables. These three tables will also be inner joined on a column.

View Replies !

Select Data Multiple Tables
I would like to SELECT information out of around 100 tables. The thing is I would like to avoid typing all those 100 names manually....

View Replies !

SELECT Data From 3 Tables And Display It As Row
How can i select data for month 04, 05 and 06 from 3 separate tables and display it as three rows.

I am trying this but it does not work.

SELECT MONTH(click_date) as Month, COUNT(*) as 'count' FROM broadband_clicks where YEAR(click_date)=2007 AND MONTH(click_date)=04 GROUP BY MONTH(click_date)

SELECT MONTH(click_date) as Month, COUNT(*) as 'count' FROM broadband_clicks where YEAR(click_date)=2007 AND MONTH(click_date)=05 GROUP BY MONTH(click_date)

SELECT MONTH(click_date) as Month, COUNT(*) as 'count' FROM broadband_clicks where YEAR(click_date)=2007 AND MONTH(click_date)=06 GROUP BY MONTH(click_date)

so basically i want something like this.

Month | count(*)
04 | 1000
05 | 980
06 | 1100

View Replies !

Select Data From Multiple Tables
I am trying to use the select statement to select data from multiple tables withing my database. could someone post me an example of how the code would look.

if you could use the following info in the string the would be great.

I want to selsect the following info....

View Replies !

Select Data From Two Tables And Order By One Of Them
I have two table book and book_images. book contains a list of books and book_images is a table that lists the books and the assigned image (there can be more than one image assigned to more than one book);

The process to assign a image to books is this. User selects the image. then from the list of books selects which one/ones they want to assign it to.

As there are over 7000 books i want to add a search. so when someone types in "Vellum" then the book Vellum will be returned. But the problem is i would also like the books that are in the book_image table.

View Replies !

Adding Missing Rows In Table In 1 Select (+ Join) Command.
Hi all! Here's what I need to do :

I have two tables :
A B
a b c d
---- ----
1 z 1 k
2 x 5 l
3 c 6 j

I need a SELECT with JOIN that would give me :
A
a b
----
1 z
2 x
3 c
5 NULL
6 NULL

so I need to add the missing rows from the A.a and B.c JOIN,
how can I do that ?

I can't use a Union 'cause I can't use MySQL version 4.

View Replies !

SELECT Search Query - Table Join Required? Help Please!! (PHP + MySQL)
I have a search form that has:
- drop down with states (nsw, vic etc)
- drop down with all business categories (retail, commercial etc)
- keyword / postcode field (2000, or 'builders')

The user gets results returned from the business table filtered by state (mandatory), which category is selected (mandatory) and by keyword (optional) or postcode (optional).

If keyword / search phrase is given then it will do search of the keywords fields of the business table (has already been indexed) in the selected category only. If postcode it will return all businesses in that category in order of distance from the given postcode.

I have 5 tables (additional fields ommitted):

'state'
state_id, name

'businesses'
business_id, keywords, name, postcode, state

'postcodes'
fromPostcode, toPostcode, distanceKMS

'categories'
category_id, name

'business2category'
business_id, category_id

Please dont thing i'm just pawning off my work here!!
Basically, i've got this working already, but only just, and in a very long and convulted format. Its far too long to post here, but since i'm not fully versed in table joins, i've been searching individual tables (e.g. SELECT *,MATCH AGAINST etc), building arrays, searching arrays again, and then building results at the end to fit into the paginator. Now the search is taking too long to perform, and i need a leaner alternative. not to mention theres way too many lines of code, and i just know theres a better way.

There must be a very simple way to achieve the following searches using table joins, can anybody please help me with 3 search examples below so i can try to understand joins better?

Search 1:
State + Category only

Search 2:
State + Category + Keyword

Search 2:
State + Category + Postcode

If there is no postcode, the others still need to display the data
filtered by distance from a default postcode of 2000.

I'd really appreciate if anybody has a few minutes free to help out here, and hopefully teach me something about effective table joins and searching. I've omitted the extra fields and tables from the real structure, and just left the relevant ones above - if theres anyhing missing or not making sense please let me know and i'll fix up asap.

View Replies !

How Do I Select Data From Two Tables In A Single Statement?
This is a very basic question. I have two tables, say "users" and "purchases":

purchases: order_id, order_user_id, items, date
users: user_id, username, email

What I want to accomplish is to compose a single query that will include the user data along with the purchase information. Something like:

SELECT order_id, order_user_id, items FROM purchases WHERE date = 3/12/09

and then translate the order_user_id into user data, so that I would get a final result of an array of rows where each row would include:

order_id, username, email, items

Is this accomplished by a JOIN statement and how is the result code written (PHP).

View Replies !

Ideal Way To Preserve All The Parent Table Entries In A Join With Many Child Tables
The problem is: How do I join a huge parent table with many child
tables (more than 5 child tables) preserving all of the parent table
entries. Lets say there is the parent table parentTable and three
child tables childTable1, childTable2, childTable3. In order to get
the data after joining these tables the query that I have been using
was:

select parent.field1, parent.field2, parent.field3, child1.field4,
child1.field5, child2.field6, child3.field7 from ParentTable parent,
childTable1 child1, childTable1 child2, childTable3 child3 where
parent.fielda = child1.fieldb and parent.fieldc = child.fieldd and
parent.fielde = child.fieldf.

Although the tables are huge (more than 100,000 entries), this query
is very fast, however those parent table entries which do not have
child entries are lost. I know that I can left join a parent table
with a child table and then with the next child table and then with
the next child table and continue. Isn't there a simple solution for
this commonly happening problem?

View Replies !

Select Count Of Data Appearing In One Table From Another Table
I have three tables:

t_Products (id, name)
t_Shop (id, location, name)
t_Carries (product, shop)

If Shop carries a product, there will be a value pair in t_Carries but otherwise no record is listed.

Is it therefore possible to return a list in MySQL showing something like this

shop.Id, product.Id, count(or something)
1 1 0
1 2 1
1 3 1
1 4 0

Or must I use two query and programmatically generate the list?

View Replies !

Select One Column From A Table That Matches Data From Other Table
how to select data from one column in a table where that data maches some other data in some other table, but that data in that other table contains only first 4 simbols of data in my initial table!

T1
aaa
bbb
ccc
ttt

T2
a
b
t

result should be
T1+T2 = T3

T3
aaa
bbb
ttt

View Replies !

Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table?

for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0.
i don't want merged results. i just need to list all therapist based on the where criteria from a different table.

these two tables have the therapist_id in common.

View Replies !

Select From 2 Tables, Even When There Nothing In Table 2
table1
======
ID

table2
=======
ID
name

SELECT FROM `table1`,`table2` WHEN
table1.ID = table2.ID

View Replies !

Select Data From One Table
I am rather new to databases and MySQL in general and have came across a problem I can't figure out. It may not be doable because of my design, then again it could be something easy that I am missing. I have the follow tables

hostnames:

hostname host_os

Server1 Windows 2000
Server2 Sun Solaris
Server3 OS X

host_ids: (Primary key is hostname and user_id combined)

hostname user_id

Server1 101
Server1 200
Server2 301
Server2 101

What I want to do is pull back the hostname from the hostnames table where the user_id 101 is not listed. I want to be able to list only the hosts that user 101 is not on but since I have the host name listed several times in the host_ids table I end up getting hosts that have user 101 on them. For example Server1 has user 101 and user 202 on it, If I do a select statment to find all servers with out user 101 on it Server1 is still going to come back in the results becuase user 200 is on it. Is there a way to accomplish only getting hostnames back that never showed user 101 on it in the host_ids table?

View Replies !

Select Data From One Table Insert To Other
Can anyone give me some hints about a query.
A query which takes a value from form field insert it to 1st table,
then select other table within that query to select name,email from
2nd table and insert it to the 1st table from 2nd table,but condition is
that in where clause it must check that dcode entered in the form value
must match with 2nd table dcode(dcode is field in table.).

View Replies !

Copying Selected Data From Two Tables Into One Table
I have a list of members in my "members" table, and they can buy tickets for an event. When they buy them, their member id number and the ticket info is stored in the "transactions" table. For the final "tickets" table, which I want to print out to put on the door, I also need to have their email and name, which is in the "members" table but not the "transactions" table.

What I want to do is insert bits of the "transactions" table into the "tickets" table, and then also use the member id number from the "transactions" table to lift out the corresponding member name and email address and insert them in as well.

Here's what I tried before I realised that I probably need something quite a bit more complicated:

INSERT INTO tickets (SELECT transaction_id, ticket_member_id, ticket_code FROM transactions), (SELECT name, email FROM members WHERE member_id=ticket_member_id)

member_id is the one in the "members" table.

Basically, here's what I want the "tickets" table to look like (the columns):

transaction_id (from "transactions")
ticket_member_id (from "transactions")
ticket_code (from "transactions")
name (from "members" using ticket_member_id)
email (from "members" using ticket_member_id)

I think I need to use a while to iterate through everything but just thought I'd check in case there's an easy way.

View Replies !

'select * From Table' Returns Truncated Data
I am in the process of creating my first MySQL DB. I have some experience with Access.

The DB I created has a main table with several "Look up" tables. While loading the look up data I ran a "SELECT * FROM network;" query. The return on the screen was not all neat and formatted like the documentation indicated. Some of the data was truncated. If I narrow the query to specific rows of data it is all there. This only happens if more than one row is displayed in the name column.

What am I doing wrong?

The two fields in the table are: ID INT(3) and name VARCHAR(20). The ID is the primary key.

View Replies !

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

View Replies !

Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site.

Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table.

The common column to both tables is the 'taskid' column.

What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?

View Replies !

Data Insertion/Update Form Different Tables Into On Table
I have 3 tables say Employees, Benefits, Employee_Benefit_Mapping. These tables are something like this:

Employee:
========
Employee_Id
Name
Dept_Id
.
.

Benefits:
=========
Benefit_Id
Benefit Name
.
.

Employee_Benefit_Mapping:
=========================
Mapping_Id
Employee_Id
Benefit_Id

Its a many to many relation. Hence the mapping table.

Now the situation and question is:
====================================
Every employee "In a certain Dept" is eligible for some benefits by default. How can add these records with one (or minimum) insert statement(s) rather than going employee by employee?

Insert into Employee_Benefit_Mapping ((Select Employee_Id from Employee where Dept_Id = xx), (Select Benefits_Id from Benefits where Benefit_Id in(xx, yy)))

Will this work to insert all the data into the mapping table?

View Replies !

Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something...

(I'm using PHP and MySQL)

When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site.

However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?

View Replies !

Determining Which Table To Query Based On Data Within Tables
I have 2 tables:

default_categories
column 1: category_id
column 2: category_name
column 3: category_parent

custom_categories
column 1: custom_cat_id
column 2: custom_cat_name
column 3: custom_cat_parent

The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table.

So if the default category has 3 rows with IDs | names:
123 | Dogs
456 | Cats
789 | Fish

And the custom category has 1 row with IDs | names:
456 | Very Cute Cats

I want my query of these 2 tables to produce the following IDs | names:
123 | Dogs
456 | Very Cute Cats
789 | Fish

I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id.

MySQL
SELECT *
FROM default_categories
LEFT JOIN custom_categories ON category_id = custom_cat_id
WHERE category_parent = ''
AND custom_cat_parent = ''
ORDER BY $order_by $sort

View Replies !

Can I Use Select To Query Mulitple Data From Within A Field Of A Table?
I would be grateful if someone could tell me whether Select is appropriate to choose data within a field and separated by commas . I have made the foll. script. What I want to achieve, is below the script.....

View Replies !

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

View Replies !

Joining Data (inner Join / Self Join?)
I am relatively new to php/mysql and I am having a problem figuring out how to do a join. I have a database with a person's name and each person has an ID. I want to be able to add their relatives by typing only their ID.

For example if person 1's descendant was person 37, I want to be able to enter that in the DB and then run a query on person 1's page so that when I have 37 entered as his descendant it will query the DB for his name and print his name but not the ID.

View Replies !

How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi,
I have two tables:

table A

Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13

table B

Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6

How can I get sum of unique keys from table A and B (id_result) like this?:

id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11

I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?

View Replies !

(select Where) Join OR (select Join) Join
which one is better, (select where) join OR (select join) join ?!

I can join two table with select and where, also i can do the same with join keyword. The result is same but which one is better?
I know that joining with join keyword is better for explicit code but what about performance?

View Replies !

Join Two Tables
I have a database that has two tables and I'd like to be able to join more than one column in one table to one column in the other table....

View Replies !

How To Join 2 Tables
Anyways Im a newbie using MYSql V5

I have 2 tables (called teams & results)

In teams I have 2 columns

teamsindex..........teamname
(Primary Key)
........1......................arsenal
........2......................man u
........3......................liverpool
........4......................chelsea
........5......................tottenham
........6......................accrington stanley (who are they :) )

In results I have 3 columns (amongst many) which contain primary key & fixture info (intiger)

e.g.

Recordnum..........hometeam.......awayteam
(primary key)

.........1......................1.......................2
.........2......................3.......................4
.........3......................5.......................6

I want to output the fixtures so instead of it displaying 1vs2, 3vs4, 4vs5 I want it to display arsenal vs man u, liverpool vs chelsea, tottenham vs accrington stanley etc

I know there is a really really simple answer to this but Im really really thick, I expect its based on the JOIN syntax but can someone show me the code that would produce the results I require in order for me to get my head around it, me is very odd, I can work things out going backwards.

View Replies !

How To Join Tables?
I would like to select info from just two tables. I've tried two seperate select calls but it gives me some of the same info from each table twice.

Can someone please post a "join for dummies" example on how I would do this.

I've tried using "Union" but I'm still just a newbie and was even more confused.

View Replies !

Join Three Tables
I have a problem about joining three tables A, B, C:

table A has the serial_number as its primary key,
table B has the serial_number as its primary key but serial_number is not unique in this table (combined with category, group etc. other columns as primary key together )
table C has some common columns with table B

Now I want to select the serial_number from table B with certain conditions from Table C, so I could use the following query:
select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions;

And then, I would like to add the serial_number in table_A but not in table B to the serial_number I just selected, so I would use something like:

select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions
UNION
select serial_number from table_A not in table_B;

is it right? I think it's the right way to do that.

Another question, with the same task, can I do the query like this:

select tA.serial_number from table_A as tA LEFT JOIN table_B as tB ON tA.serial_number = tB.serial_number JOIN tableC as tC WHERE Conditions..

That's what I understand about "LEFT JOIN", SQL will select serial_number from table_B and table_C with the conditions satisfied first and then join table_A with serial_number only in table_A but not in table_B. Am I right?

View Replies !

Trying To Join 3 Tables
I ma trying to create a join on 3 tables. However I can't get it to work.

If I do this it works

SELECT * FROM Cottage, Cottage_Photos, Location WHERE Cottage.Cottage_ID = Cottage_Photos.Cottage_ID AND Cottage.Location_ID = Location.Location_ID;

However once i try this i just keep getting an empty set result

SELECT * FROM Cottage, Cottage_Photos, Location WHERE Cottage.bedrooms = '3Ã¢â‚¬â„¢ AND Cottage.Cottage_ID = Cottage_Photos.Cottage_ID AND Cottage.Location_ID = Location.Location_ID;

Is it possible to join 3 tables?

View Replies !

Join Tables With Ip
i want to join two tables using ip address through sql

select * from db1.table1 union select * from db2.table2;

this is used for join two tables from the same pc. what command is used for joining the tables if they are not in same pc ?

View Replies !

Join Four Tables
SELECT appointment.appt_id, patient.pt_fname, patient.pt_lname, patient.pt_phonenum, appointment.appt_date, appointment.appt_time, language.language FROM appointment, language, patient where patient.lang_id = language.lang_id and patient.pt_id = appointment.pt_id

added requester to sql statement

SELECT appointment.appt_id, patient.pt_fname, patient.pt_lname, requester.req_fname, requester.req_lname, patient.pt_phonenum, appointment.appt_date, appointment.appt_time, language.language FROM appointment, language, patient where patient.lang_id = language.lang_id and patient.pt_id = appointment.pt_id and requester.req_id = patient.req_id

View Replies !