link table links the same table to itself

The database describes some cables with exactly on plug at each end.
The question is how to write a query that will return the id for the plug at the other end of the cable assuming we only know the id for the plug at this end. We do not know the cable id.
the plug tables have two columns, id and cable_id
the cable table has one column, id.
Any takers
Mike

Trouble linking two tables...
&nbsp(117 Views)
Im having a little trouble getting the right output with some SQL im trying to write.
Essentially theres two tables. A table of files and a table of permissions.
File
fileid
filepublic
filename
Permissions
permid
permfileid
permuid
I want to output all file records that have filepublic=0 or where there is a permissions record for the logged in user.
I was thinking of something like:
SELECT fileid, filename, COUNT(permid) AS num
FROM files
LEFT OUTER JOIN permissions ON (fileid=permfileid AND permuid='$uid')
WHERE (filepublic=0 OR num>0)
But obviously you cannot use num within the WHERE clause :S
Any ideas

Web link to database
&nbsp(126 Views)
every one,
I am working on a database that needs to link to a database one an individual signs in and also when they register their information.
Please assist.
Thanx

Mysql table check, foreign key & linking table question(s)
&nbsp(112 Views)
I have created a basic database and have a few questions.
The purpose of the database is to track baseball stats for each player in the database by game.
My Questions:
1. Would anyone mind taking a look at the way i have my tables set up and let me know if I could improve upon the column types at all I have gotten "best practice" ideas for most but, really wanted more input/suggestions in case it can be improved upon.
2. I understand how the first two tables link together with Primary Key/FK. When I wrote a few queries that pulled data from the first three tables, I never once referenced the fourth table. The queries seemed to work. However, someone else suggested that I needed the 4th table as a linking table. I am wondering if this is really true. I don't want there to be any issues w/ referential integrity but I am not understanding why I need the link table.
Anyone that could help or shed some light, I would be very grateful.
Here are the statements I wrote for each table.
CREATE TABLE `player` (
`playerID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`userName` varchar(65) NOT NULL,
`password` varchar(65) NOT NULL,
`playerFN` varchar(48) NOT NULL,
`playerLN` varchar(48) NOT NULL,
`jerseyNum` int(11) NOT NULL,
`contactName` varchar(48) NOT NULL,
`contactNumber` varchar(12) NOT NULL,
`position` varchar(100) NOT NULL,
`bats` enum('L','R','B') NOT NULL,
PRIMARY KEY (`playerID`),
ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |
CREATE TABLE `baseball`.`stats` (
`statID` TINYINT( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`playerID` TINYINT( 4 ) UNSIGNED NOT NULL , (Foreign Key)
`atBats` TINYINT( 4 ) NOT NULL ,
`hits` TINYINT( 4 ) NOT NULL ,
`walks` TINYINT( 4 ) NOT NULL ,
`RBI` TINYINT( 4 ) NOT NULL ,
`strkOuts` TINYINT( 4 ) NOT NULL ,
`singles` TINYINT( 4 ) NOT NULL ,
`doubles` TINYINT( 4 ) NOT NULL ,
`triples` TINYINT( 4 ) NOT NULL ,
`homeRuns` TINYINT( 4 ) NOT NULL ,
`runsScrd` TINYINT( 4 ) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE `tournaments` (
`tourneyID` int(3) unsigned NOT NULL AUTO_INCREMENT,
`tourneyName` varchar(30) NOT NULL,
`gameDate` date NOT NULL,
PRIMARY KEY (`tourneyID`),
ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |
CREATE TABLE `tourneyplayers` (
`tourneyPlayerID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`playerID` int(11) unsigned NOT NULL,
`tourneyID` int(11) unsigned NOT NULL,
PRIMARY KEY (`tourneyPlayerID`),
CONSTRAINT `tourneyplayers_ibfk_1` FOREIGN KEY (`playerID`) REFERENCES `player` (`playerID`) ON DE
LETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `tourneyplayers_ibfk_2` FOREIGN KEY (`tourneyID`) REFERENCES `tournaments` (`tourneyID`
) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |

Self-linking child>parent categories and breadcrumbs
&nbsp(257 Views)
I have a table with the following structure that I have to use:
Code:
DROP TABLE IF EXISTS `invMarketGroups`;
CREATE TABLE `invMarketGroups` (
`marketGroupID` int(11) NOT NULL default '0',
`parentGroupID` int(11) default NULL,
`marketGroupName` char(100) default NULL,
`description` varchar(3000) default NULL,
`graphicID` int(11) default NULL,
`hasTypes` int(11) default NULL,
PRIMARY KEY (`marketGroupID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
As you can see, it references itself (parentGroupID will be the same as marketGroupID of another row) to create a tree structure. Is it possible to create a query to get all of the parents above a specific node so that I can create a breafcrumb in one query I know that I can create a loop in PHP to keep querying until it reaches the top, but I would like to do it in one query

Still Need help with linking strings to tables please - with diagram!
&nbsp(153 Views)
Gusy i am still struggling with this problem.
I have added a diagram now to help ppl see the issue..
THe company holds a string, which i compare against the postcode table to get a match and a zone.
hope this help.
please help me, i will pay gold to the person who does!
i am currently upto this... but its not working...
$query_qryPostCodes = "SELECT ZoneID FROM postcodes WHERE PostCode LIKE SUBSTRING(" . $row_qryCompanyDetails['CompanyPostCode'] . ",1,CHAR_LENGTH(PostCode))";

Nested links table
&nbsp(230 Views)
I'm actually totally lost and confused at this.
In the past I have created a table for nested link's with a table with a PARENT ID and CHILD ID.
But now it's broken down into table, like CATEGORY ID and SUBCATEGORY ID.
That bit is fine, but I get stuck with this part, SOME subcategory id's have sub-subcategory id's.
But what is the best way to create this these tables to ensure they run at their best and all data is brought back how you want it

Displaying Time values from a linked MySQL table in MS Access
&nbsp(162 Views)
When I link a MySQL table containing a TIME column to Microsoft Access the value does not display. If I edit the value it updates correctly in mysql but if I refresh the Access datasheet view the value disappears and the
column shows as blank.
Can anyone tell me how to display time values in Access.
I am using MySQL ODBC driver 3.51, MySQL 4, Access 2002
Thanx,
Rob!

Db security for link submission site
&nbsp(126 Views)
What's the best way to minimize potential risks like sql injection attacks, when site visitors add a link that goes directly into the database
I can't just block all the characters since too many websites have all sorts of characters in their links. Do I just delete some that never seem to be used like quotes, etc.

Alphbetical links
&nbsp(224 Views)
I'm sure this is a common occurrence, so there must be a common approach...
I have a table with a column that contains a word.
I'd like to have a row of 26 letters representing the first letter of the word. If the table contains any words that begin with that letter, the letter will link to a page that queries the db and presents all those words that begin with that letter.
A B C D E F G H I J ...
So, the question how to populate the row of letters.
Most obviously, I could run 26 queries to see which letters are represented.
Is this the best or only way

Sql join (?) selecting from multiple linked tables
&nbsp(230 Views)
Hi ,
I am trying to select from these tables:
(seems I am not allowed to post links or images, so if you could be so kind if checking out the db schema at gliffy.com/pubdoc/1262894/L.jpg, it would be easier to see what I am looking for)
I'm trying to write a query that, given a arbitrary id_tabla_enum, would answer back with:
cf_cuadrante.id_fila, cf_cuadrante.id_colu,
cf_cuadrante.valor,
cf_fila.nombre, cf_fila.posicion
cf_colu.nombre, cf_colu.posicion,
I wont post the queries I am testing, because they are so bad it's not even funny.
Hopefully the case is somewhat clear from what I posted and someone can lend a hand, at least cluing me in in what part of the FM should I be consulting.

Search across linked tables
&nbsp(237 Views)
G'day all,
After everybody help I managed to get my main table accessing keys from several other tables.
Now comes the hard part ... trying to get data out from a search
I have the following SELECT statement which will work when one of the FROM's comes into play but one I have it run 2+ it causes a ERROR 28 on the server (takes about 30/60 seconds) I am assuming that I have stuffed something and it is trying to grab way too much data
Code:
SELECT
V.`vid`,
V.`name`,
V.`add1`,
V.`add2`,
V.`suburb`,
V.`state`,
V.`postcode`,
V.`phone`,
V.`fax`,
V.`openhours`,
V.`email`,
V.`www`,
V.`microimage`,
L.`location`,
C1.`cuisine` AS `cuisine1`,
C2.`cuisine` AS `cuisine2`,
S.`style`
FROM
`venue` AS V,
`L_Location` AS L,
`L_Style` AS S,
`L_Cuisine` AS C1,
`L_Cuisine` AS C2
WHERE V.`name` LIKE '%$search%'
OR V.`suburb` LIKE '%$search%'
OR V.`postcode` LIKE '%$search%'
OR L.`location` LIKE '%$search%'
OR S.`style` LIKE '%$search%'
OR C1.`cuisine` LIKE '%$search%'
OR C2.`cuisine` LIKE '%$search%'
AND V.`ftype1` = C1.`lcid`
AND V.`ftype2` = C2.`lcid`
AND V.`location` = L.`llid`
AND V.`style` = S.`lsid`
ORDER BY V.`name` ASC
What I am trying to do as you have probably guessed is search my database. Now I have read that FULLTEXT is the way to go but everythign I have read says that the fields for FULLTEXT searching need to be text - I can't do that (I think) because some of my feilds are keys

Advice on links database structure
&nbsp(378 Views)
I'm creating a 'Favourite Links' function on our Intranet, using PHP/MySQL. I'd really like some advice on the most efficient way of setting this up.
Users login to the site and are either identified by a cookie or by a session. Their favourite links (ID, Title, URL, Description) will be displayed on their personalised homepage.
We have about 230 users. I could set up a a table to hold the links for each user but that seems excessive. I could create the one table of links but with a field that records the owner/users ID but perhaps that would be a very memory-hungry query once the table gets really large.
If anyone's had experience with this and can share some advice, that'd be great.
Nick

Link table, linking multiple addresses to a single client
&nbsp(119 Views)
dear all,
i have the following tables:
client - table of client details, name etc
property - table of addresses, coordinates etc
pro2cli - a link table, containing id's of clients and id's of properties they are associated with
this is so i can associate many addresses witha single client (home, work, etc).
can someone help me out with a query to select a single client, and all of the addresses they are associated with (if any).
i have played with joins and stuff, but just need a little nudge in the right direction i think as i am not getting the results i excpet
many thanks in advance.
what i have tried...
Code:
$sql = "SELECT * FROM client
LEFT JOIN pro2cli ON pro2cli.p2c_cli = client.cli_id
LEFT JOIN property ON pro2cli.p2c_pro = property.pro_id
WHERE cli_id = '".$cli_id."'";
- only shows me a single property, even through the client has 2 in the link table

how do I set up a link?
&nbsp(226 Views)
I am having to construct a database by hand from a specification given to us by an outside client. Their table layout examples have:
Field, Attribute, Type, Link.
In link are (as example) MOD_CODE and MOD_TYPE - both are other tables in the database. Are these foreign keys And if so what do I need to do to set up those links

populating a link table
&nbsp(225 Views)
,
I'm wondering if a link table can be populated automatically Here's the example
Table1 is a list of movies (movie_id, name)
Table2 is a list of producers (producer_id, name)
Table3 is the link table (movie_id, producer_id)
When I insert into both the movies and the producers table, is there a way that the link table can be inserted with both ids automatically or do I have to do a select on both tables, then insert into Table3
I assume once the database is populated, I will have to do selects on the producer table to see if he/she already exists, but for the time being, I'd like to know based on inserting into both tables.

Using a left join with 2 values linked to the same tables
&nbsp(255 Views)
,
I have a table that has two values, author_id and approver_id. Both of these refer to the value id in my users table.
Is it possible to use a left join and have the query get the author and approver name (also in the users table) Or will I have to use multiple queries (Can't use nested selects, this particular server is still running MySQL3 -sigh-).

linking stores with products (was "MySQL dificult code")
&nbsp(110 Views)
Hi !
I have a MySQL database dificult (for me anyway..).
I have a ecommerce site with a table of products.
Now I want to create a new table with a list of stores where clients can find near home those products. In a limit situation I can have a list of all products indexed in one store (and the products list is increasing everyday, the same with the list of store...). I want this feature to be capable of introdution of new products in a store that already as several products.
My idea is to develop 3 tables:
Products
+----+-----------+--------------+
| id | name | description |
+----+-----------+--------------+
| 1 | xxxxx | aasa... |
| 2 | vvvvvv | asasasasas... |
+----+-----------+--------------+
Stores
+----+-------------------+-----------+
| id | store | address |
+----+-------------------+----------+
| 1 | aaaaa | street1... |
| 2 | bbbbbb | street2... |
+----+-------------------+-----------+
Linking stores with products
+----+-------------------+-------------+
| id | store id | products id |
+----+-------------------+-------------+
| 1 | 1 | 1;2 |
| 2 | 2 | 2 |
+----+-------------------+-------------+
Can I implement this in MySQL I mean how can (if I can) place several ids in a field and how can I do the query in order to list the products inside a store
Hope that I made my self clear. If not please do not hesitate to request further information.

Setting up a database and linking it to a website ?
&nbsp(221 Views)
Does anyone know how to setup a database linked to a website please I would like to setup a database so that when someone fills in their first name and email address to receive newsletters, it can automatically go into a database to be used later to email them their newsletters.
The (mysql ), database in my cpanel is phpmyadmin version 2.8.0.2

Calling links from a database
&nbsp(473 Views)
Can anyone tell me how I links should be inputed into a MySQL database Also, how do I call the link in a web page I do not want the URL displayed, but want something like the example below done in HTML:
Click here!

Disclaimer : SolveErrors.com is an independent online IT support service provider company for third party product. The brands, Names, Images, Trademarks, Products & services of third parties mentioned on this website are only for reference and to furnish information. SolveErrors has no affiliation with any of these third-party companies unless such relationship is expressly specified. The services we offer may also be available on the brand owner's website.